/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package azure;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Random;

/**
 * Use SQLite as the database instead of XML; much easier to work with, and infinitely more flexible. This class handles all database
 * functionality.
 * Performance remains to be seen, and extra Java library required, but hell it's easier than using XML!
 * @author Gargravarr
 * @date Aug 8, 2011
 */
public class SQLibrarian
{

    private final String SQLDBNAME = "library.sqlite";
    private Connection sqlConn;
    private SimpleDateFormat dateFormatter = new SimpleDateFormat("yyyy-MM-dd");

    public SQLibrarian()
    {
	try
	{
	    Class.forName("org.sqlite.JDBC"); //Import the SQLite database driver
	    sqlConn = DriverManager.getConnection("jdbc:sqlite:" + SQLDBNAME);
	}
	catch (Exception se)
	{
	    ErrorLogger.logErrorMessage(this.getClass().getName(), 37, "Class initalisation failed: " + se.getMessage());
	}
    }

    public String getLibraryKeyString()
    {
	try
	{
	    Statement getKey = sqlConn.createStatement();
	    ResultSet libraryKeySet = getKey.executeQuery(StoredProcs.selectLibraryKey());
	    if (libraryKeySet.getString("SettingValue").length() > 0)
	    {
		return libraryKeySet.getString("SettingValue");
	    }
	    else
	    {
		libraryKeySet.close();
		byte[] libraryKey = new byte[16];
		Random rng = new Random(System.currentTimeMillis());
		rng.nextBytes(libraryKey);
		StringBuilder sb = new StringBuilder();
		for (Byte b : libraryKey)
		{
		    sb.append(String.format("%x", b));
		}
		getKey.executeUpdate("UPDATE Settings SET SettingValue='" + sb.toString() + "' WHERE SettingName='LibraryKey'");
		getKey.close();
		return sb.toString();
	    }
	}
	catch (Exception se)
	{
	    ErrorLogger.logErrorMessage(this.getClass().getName(), 71, se.getMessage());
//	    System.err.println("SQL Connection failed: " + se.getMessage());
	    return "";
	}
    }

    /**
     * 
     * @return 
     */
    public ArrayList<Manga> loadFromDB()
    {
	try
	{
	    Statement selectManga = sqlConn.createStatement();
	    ResultSet mangas = selectManga.executeQuery(StoredProcs.selectAllMangas());
	    ArrayList<Manga> mangaList = new ArrayList<Manga>();
	    while (mangas.next())
	    {
		Manga m = new Manga();
		m.mangaID = mangas.getInt("MangaID");
		m.libraryID = mangas.getInt("ID");
		m.mangaName = mangas.getString("MangaName");
		m.description = mangas.getString("Description");
		m.publisherID = mangas.getInt("Publisher");
		m.publishedYear = mangas.getInt("PublishedYear");
		m.genre = mangas.getInt("GenreID");
		Statement selectVolumes = sqlConn.createStatement();
		ResultSet volumes = selectVolumes.executeQuery(StoredProcs.selectAllVolumesForLibraryID(m.libraryID));
		while (volumes.next())
		{
		    Volume v = new Volume(volumes.getInt("VolumeID"), volumes.getInt("VolumeNo"), volumes.getInt("VolumeYear"), volumes.getString("VolumeDescription"));
		    Statement selectChapters = sqlConn.createStatement();
		    ResultSet chapters = selectChapters.executeQuery(StoredProcs.selectChaptersForLibraryIDAndVolume(m.libraryID, v.volumeID));
		    while (chapters.next())
		    {
			Chapter c = new Chapter();
			c.chapterID = chapters.getInt("ChapterID");
			c.chapterNo = chapters.getInt("ChapterNo");
			c.chapterVersion = chapters.getInt("ChapterVersion");
			Statement selectPages = sqlConn.createStatement();
			ResultSet pages = selectPages.executeQuery(StoredProcs.selectAllPagesForLibraryIDAndChapter(m.libraryID, c.chapterID));
			while (pages.next())
			{
			    Page mangaPage = new Page();
			    mangaPage.pageID = pages.getInt("PageID");
			    mangaPage.pageNo = pages.getInt("PageNo");
			    if (pages.getString("LastReadDate") != null)
			    {
				mangaPage.lastReadDate = dateFormatter.parse(pages.getString("LastReadDate"));
			    }
			    mangaPage.filename = pages.getString("Filename");
			    c.pages.add(mangaPage);
			}
			pages.close();
			selectPages.close();
			v.chapters.add(c);
		    }
		    chapters.close();
		    selectChapters.close();
		    m.volumes.add(v);
		}
		volumes.close();
		selectVolumes.close();
		mangaList.add(m);
	    }
	    mangas.close();
	    selectManga.close();
	    return mangaList;
	}
	catch (Exception e)
	{
	    ErrorLogger.logErrorMessage(this.getClass().getName(), 139, e.getMessage());
//	    System.err.println("SQL Statement failed: " + e.getMessage());
	}
	return null;
    }

    /**
     * Update the stored library.
     * Yes, this is complicated, but it does a LOT.
     * First off, this uses SQLite transactions to improve performance. The query Strings are stored in the
     * StoredProcs class, since they're used like Stored Procedures here. It also makes
     * this a little easier to read. There are two conditions for each object; either it already exists and should
     * be updated, or it's new and needs to be inserted. Existing objects that haven't changed,
     * as indicated by a boolean flag, are ignored. The program knows if the object is new
     * because it stores its database ID within the object. If it's zero (default), then it's new
     * @param mangaLibrary The manga library from program memory
     */
    public void saveLibraryToDB(ArrayList<Manga> mangaLibrary)
    {
	try
	{
	    //PreparedStatements are meant to be faster than individual queries, so stack them here, load them up sequentially,
	    //then throw them all into the Library at once
	    PreparedStatement updateMangas = sqlConn.prepareStatement(StoredProcs.getUpdatePreparedStatement(Enums.MANGA));
	    PreparedStatement updateVolumes = sqlConn.prepareStatement(StoredProcs.getUpdatePreparedStatement(Enums.VOLUME));
	    PreparedStatement updateChapters = sqlConn.prepareStatement(StoredProcs.getUpdatePreparedStatement(Enums.CHAPTER));
	    PreparedStatement updatePages = sqlConn.prepareStatement(StoredProcs.getUpdatePreparedStatement(Enums.PAGE));
	    PreparedStatement insertManga = sqlConn.prepareStatement(StoredProcs.getInsertPreparedStatement(Enums.MANGA));
	    PreparedStatement insertVolume = sqlConn.prepareStatement(StoredProcs.getInsertPreparedStatement(Enums.VOLUME));
	    PreparedStatement insertChapter = sqlConn.prepareStatement(StoredProcs.getInsertPreparedStatement(Enums.CHAPTER));
	    PreparedStatement insertPage = sqlConn.prepareStatement(StoredProcs.getInsertPreparedStatement(Enums.PAGE));
	    sqlConn.setAutoCommit(false); //Allow committing of a large batch at once
	    /***************************/
	    for (Manga m : mangaLibrary)
	    {
		if ((m.libraryID > 0) && (m.isModified))
		{
		    updateMangas.setString(1, m.mangaName);
		    updateMangas.setString(2, m.description);
		    updateMangas.setInt(3, m.publisherID);
		    updateMangas.setInt(4, m.publishedYear);
		    updateMangas.setInt(5, m.genre);
		    updateMangas.setInt(6, m.libraryID);
		    updateMangas.executeUpdate();
		    m.isModified = false;
		}
		else if (m.libraryID == 0)
		{
		    m.libraryID = sqlConn.createStatement().executeQuery(StoredProcs.getMaxMangaID()).getInt("ID") + 1;
		    insertManga.setInt(1, m.libraryID);
		    insertManga.setString(2, m.mangaName);
		    insertManga.setString(3, m.description);
		    insertManga.setInt(4, m.publisherID);
		    insertManga.setInt(5, m.publishedYear);
		    insertManga.setInt(6, m.genre);
		    insertManga.executeUpdate();
		    m.isModified = false;
		}
		saveVolumes(m, insertVolume, updateVolumes, insertChapter, updateChapters, insertPage, updatePages);
	    }
	    sqlConn.commit();
	    sqlConn.setAutoCommit(true);
	    updateMangas.close();
	    updateVolumes.close();
	    updateChapters.close();
	    updatePages.close();
	    insertManga.close();
	    insertVolume.close();
	    insertChapter.close();
	    insertPage.close();
	}
	catch (Exception se)
	{
	    ErrorLogger.logErrorMessage(this.getClass().getName(), 212, se.getMessage());
//	    System.err.println("Save to library failed: " + se.getMessage());
	}
    }

    private void saveVolumes(Manga m, PreparedStatement insertVolumes, PreparedStatement updateVolumes, PreparedStatement insertChapter, PreparedStatement updateChapters, PreparedStatement insertPage, PreparedStatement updatePages) throws Exception
    {
	for (Volume v : m.volumes)
	{
	    if ((v.volumeID > 0) && (v.isModified))
	    {
		updateVolumes.setInt(1, v.volumeNo);
		updateVolumes.setString(3, v.volumeDescription);
		updateVolumes.setInt(2, v.volumeYear);
		updateVolumes.setInt(4, v.volumeID);
		updateVolumes.executeUpdate();
		v.isModified = false; //Unset the modification flag
	    }
	    else if (v.volumeID == 0)
	    {
		insertVolumes.setInt(1, m.libraryID);
		insertVolumes.setInt(2, v.volumeNo);
		insertVolumes.setString(3, v.volumeDescription);
		insertVolumes.setInt(4, v.volumeYear);
		insertVolumes.executeUpdate();
		v.volumeID = sqlConn.createStatement().executeQuery(StoredProcs.getMaxVolumeID()).getInt("ID");
	    }
	    saveChapters(v, insertChapter, updateChapters, insertPage, updatePages, m.libraryID);
	}
    }

    /**
     * Commit a Chapter and its pages to the Library. If the chapter does not exist, it will be added. If it exists and has been modified,
     * it will be updated
     * @param m The Manga to get Chapter values from
     * @param insertChapter PreparedStatement to create a Chapter entry
     * @param updateChapters PreparedStatement to update an existing Chapter entry
     * @param insertPage PreparedStatement to create a Page entry, used in the submethod
     * @param updatePages PreparedStatement to update an existing Page, used in the submethod
     * @throws Exception 
     */
    private void saveChapters(Volume v, PreparedStatement insertChapter, PreparedStatement updateChapters, PreparedStatement insertPage, PreparedStatement updatePages, int mangaLibraryID) throws Exception
    {
	for (Chapter c : v.chapters)
	{
	    if ((c.chapterID > 0) && (c.isModified))
	    {
		updateChapters.setInt(1, c.chapterNo);
		updateChapters.setInt(2, c.chapterVersion);
		updateChapters.setString(3, c.chapterDescription);
		updateChapters.setInt(4, v.volumeID);
		updateChapters.setInt(5, c.chapterID);
		updateChapters.executeUpdate();
		c.isModified = false; //Unset the modification flag
	    }
	    else if (c.chapterID == 0)
	    {
		insertChapter.setInt(1, c.chapterNo);
		insertChapter.setInt(2, mangaLibraryID);
		insertChapter.setInt(3, v.volumeID);
		insertChapter.setInt(4, c.chapterVersion);
		insertChapter.executeUpdate();
		c.chapterID = sqlConn.createStatement().executeQuery(StoredProcs.getMaxChapterID()).getInt("ID");
	    }
	    savePages(c, insertPage, updatePages, mangaLibraryID);
	}
    }

    /**
     * Commit a Page to the Library. If the Page does not exist, it will be created. If it exists and has been modified, it will be updated.
     * @param c The chapter to get Page values from
     * @param insertPage PreparedStatement object to load up with Pages to create
     * @param updatePages PreparedStatement object to load up with existing Pages to update
     * @param libraryID The library ID to save these against
     * @throws Exception 
     */
    private void savePages(Chapter c, PreparedStatement insertPage, PreparedStatement updatePages, int libraryID) throws Exception
    {
	for (Page p : c.pages)
	{
	    if ((p.pageID > 0) && (p.isModified))
	    {
		updatePages.setInt(1, p.pageNo);
		updatePages.setString(2, p.filename);
		updatePages.setString(3, dateFormatter.format(p.lastReadDate));
		updatePages.setInt(4, p.pageID);
		updatePages.executeUpdate();
		p.isModified = false;
	    }
	    else if (p.pageID == 0)
	    {
		insertPage.setInt(1, libraryID);
		insertPage.setInt(2, c.chapterID);
		insertPage.setInt(3, p.pageNo);
		insertPage.setString(4, p.filename);
		insertPage.executeUpdate();
	    }
	}
    }

    public void saveSettingsToDB(AppSettings s)
    {
	try
	{
	    PreparedStatement prep = sqlConn.prepareStatement(StoredProcs.getUpdatePreparedStatement(Enums.SETTINGS));
	    sqlConn.setAutoCommit(false);
	    /***************************/
	    prep.setString(2, "Language");
	    prep.setString(1, s.language.toString());
	    prep.executeUpdate();
	    /***************************************/
	    prep.setString(2, "DefaultDirectory");
	    prep.setString(1, s.defaultPath);
	    prep.executeUpdate();
	    /***************************************/
	    prep.setString(2, "AutoResume");
	    prep.setString(1, ((s.autoResume) ? "True" : "False"));
	    prep.executeUpdate();
	    /***************************************/
	    prep.setString(2, "LastOpenMangaID");
	    prep.setInt(1, s.lastOpenMangaID);
	    prep.executeUpdate();
	    /***************************************/
	    prep.setString(2, "LastOpenMangaChapterID");
	    prep.setInt(1, s.lastOpenChapterID);
	    prep.executeUpdate();
	    /***************************************/
	    prep.setString(2, "LastOpenVolumeID");
	    prep.setInt(1, s.lastOpenVolumeID);
	    prep.executeUpdate();
	    /***************************************/
	    prep.setString(2, "LastOpenPageID");
	    prep.setInt(1, s.lastOpenPageID);
	    prep.executeUpdate();
	    /***************************************/
	    prep.setString(2, "WindowX");
	    prep.setInt(1, s.x);
	    prep.executeUpdate();
	    /***************************************/
	    prep.setString(2, "WindowY");
	    prep.setInt(1, s.y);
	    prep.executeUpdate();
	    /***************************************/
	    prep.setString(2, "WindowWidth");
	    prep.setInt(1, s.width);
	    prep.executeUpdate();
	    /***************************************/
	    prep.setString(2, "WindowHeight");
	    prep.setInt(1, s.height);
	    prep.executeUpdate();
	    /***************************************/
	    prep.setString(2, "ImageLabelX");
	    prep.setInt(1, s.imageLabelX);
	    prep.executeUpdate();
	    /***************************************/
	    prep.setString(2, "ImageLabelY");
	    prep.setInt(1, s.imageLabelY);
	    prep.executeUpdate();
	    /****************/
	    sqlConn.commit();
	    sqlConn.setAutoCommit(true);
	    prep.close();
	}
	catch (Exception se)
	{
	    ErrorLogger.logErrorMessage(this.getClass().getName(), 377, se.getMessage());
	}
    }

    /**
     * Build the database
     * @return True if the database was created successfully, false if not
     */
    public boolean createDatabase()
    {
	try
	{
	    Statement sqlStat = sqlConn.createStatement();
	    for (String creationString : StoredProcs.getDatabaseCreationStrings())
	    {
		sqlStat.executeUpdate(creationString);
	    }
	    PreparedStatement settingsPopulator = sqlConn.prepareStatement(StoredProcs.getInsertPreparedStatement(Enums.SETTINGS));
	    sqlConn.setAutoCommit(false);
	    //It's easier to assume the presence of the settings store, so create it
	    for (String setting : StoredProcs.getSettingsStrings())
	    {
		settingsPopulator.setString(1, setting);
		settingsPopulator.setString(2, "");
		settingsPopulator.executeUpdate();
	    }
	    sqlConn.commit();
	    sqlConn.setAutoCommit(true);
	    sqlStat.close();
	    return true;
	}
	catch (Exception se)
	{
	    ErrorLogger.logErrorMessage(this.getClass().getName(), 410, se.getMessage());
	    return false;
	}
    }

    public int clearLibrary()
    {
	try
	{
	    Statement sqlStat = sqlConn.createStatement();
	    sqlStat.addBatch(StoredProcs.getDeleteAllStatement(Enums.PAGE));
	    sqlStat.addBatch(StoredProcs.getDeleteAllStatement(Enums.VOLUME));
	    sqlStat.addBatch(StoredProcs.getDeleteAllStatement(Enums.CHAPTER));
	    sqlStat.addBatch(StoredProcs.getDeleteAllStatement(Enums.MANGA));
	    int affectedRows = 0;
	    for (int i : sqlStat.executeBatch())
	    {
		if (i == 0)
		{
		    return i;
		}
		affectedRows += i;
	    }
	    sqlStat.close();
	    return affectedRows;
	}
	catch (Exception se)
	{
	    ErrorLogger.logErrorMessage(this.getClass().getName(), 438, se.getMessage());
//	    System.err.println(se.getMessage());
	    return 0;
	}
    }

    /**
     * Close the connection to the SQLite datafile
     */
    public void closeConn()
    {
	try
	{
	    sqlConn.close();
	}
	catch (Exception se)
	{
	    ErrorLogger.logErrorMessage(this.getClass().getName(), 455, se.getMessage());
//	    System.err.println("SQL Connection could not be closed: " + se.getMessage());
	}
    }

    /**
     * Load the program settings from the table and return them as an object
     * @return 
     */
    public AppSettings loadSettings()
    {
	AppSettings s = new AppSettings();
	try
	{
	    //<editor-fold defaultstate="collapsed" desc="Load and set settings">
	    Statement stat = sqlConn.createStatement();
	    ResultSet settings = stat.executeQuery("SELECT * FROM Settings");
	    while (settings.next())
	    {
		if (settings.getString("SettingName").equals("Language"))
		{
		    s.language = (settings.getString("SettingValue").equals("EN")) ? AppSettings.Language.EN : AppSettings.Language.DEFAULT;
		}
		else if (settings.getString("SettingName").equals("DefaultDirectory"))
		{
		    s.defaultPath = settings.getString("SettingValue");
		}
		else if (settings.getString("SettingName").equals("LastOpenMangaID"))
		{
		    s.lastOpenMangaID = settings.getInt("SettingValue");
		}
		else if (settings.getString("SettingName").equals("LastOpenVolumeID"))
		{
		    s.lastOpenVolumeID = settings.getInt("SettingValue");
		}
		else if (settings.getString("SettingName").equals("LastOpenMangaChapterID"))
		{
		    s.lastOpenChapterID = settings.getInt("SettingValue");
		}
		else if (settings.getString("SettingName").equals("LastOpenPageID"))
		{
		    s.lastOpenPageID = settings.getInt("SettingValue");
		}
		else if (settings.getString("SettingName").equals("WindowX"))
		{
		    s.x = settings.getInt("SettingValue");
		}
		else if (settings.getString("SettingName").equals("WindowY"))
		{
		    s.y = settings.getInt("SettingValue");
		}
		else if (settings.getString("SettingName").equals("WindowWidth"))
		{
		    s.width = settings.getInt("SettingValue");
		}
		else if (settings.getString("SettingName").equals("WindowHeight"))
		{
		    s.height = settings.getInt("SettingValue");
		}
		else if (settings.getString("SettingName").equals("AutoResume"))
		{
		    s.autoResume = Boolean.parseBoolean(settings.getString("SettingValue"));
		}
		else if (settings.getString("SettingName").equals("ImageLabelX"))
		{
		    s.imageLabelX = settings.getInt("SettingValue");
		}
		else if (settings.getString("SettingName").equals("ImageLabelY"))
		{
		    s.imageLabelY = settings.getInt("SettingValue");
		}
	    }
	    //</editor-fold>
	    stat.close();
	    return s;
	}
	catch (Exception se)
	{
	    ErrorLogger.logErrorMessage(this.getClass().getName(), 533, se.getMessage());
	    return null;
	}
    }

    public boolean deleteMangaFromLibrary(Manga m)
    {
	try
	{
	    Statement stat = sqlConn.createStatement();
	    if (stat.executeUpdate(StoredProcs.getDeleteStatement(Enums.MANGA, m.libraryID)) > 0)
	    {
		for (Volume v : m.volumes)
		{
		    deleteVolumeFromLibrary(v);
		}
		return true;
	    }
	}
	catch (Exception e)
	{
	    ErrorLogger.logErrorMessage(this.getClass().getName(), 550, e.getMessage());
	}
	return false;
    }

    public boolean deleteVolumeFromLibrary(Volume v)
    {
	try
	{
	    Statement stat = sqlConn.createStatement();
	    if (stat.executeUpdate(StoredProcs.getDeleteStatement(Enums.VOLUME, v.volumeID)) > 0)
	    {
		for (Chapter c : v.chapters)
		{
		    deleteChapterFromLibrary(c);
		}
		return true;
	    }
	}
	catch (Exception e)
	{
	    ErrorLogger.logErrorMessage(this.getClass().getName(), 575, e.getMessage());
	}
	return false;
    }

    public boolean deleteChapterFromLibrary(Chapter c)
    {
	try
	{
	    Statement stat = sqlConn.createStatement();
	    if (stat.executeUpdate(StoredProcs.getDeleteStatement(Enums.CHAPTER, c.chapterID)) > 0)
	    {
		for (Page p : c.pages)
		{
		    stat.addBatch(StoredProcs.getDeleteStatement(Enums.PAGE, p.pageID));
		}
		stat.executeBatch();
		return true;
	    }
	}
	catch (Exception e)
	{
	    ErrorLogger.logErrorMessage(this.getClass().getName(), 597, e.getMessage());
	}
	return false;
    }
    
    public boolean deletePageFromLibrary(Page p)
    {
	try
	{
	    Statement stat = sqlConn.createStatement();
	    if (stat.executeUpdate(StoredProcs.getDeleteStatement(Enums.PAGE, p.pageID)) > 0)
	    {
		return true;
	    }
	}
	catch (Exception e)
	{
	    ErrorLogger.logErrorMessage(this.getClass().getName(), 614, e.getMessage());
	}
	return false;
    }

    public ArrayList<Publisher> getPublishers()
    {
	try
	{
	    Statement selectPublishers = sqlConn.createStatement();
	    ResultSet publishers = selectPublishers.executeQuery(StoredProcs.getPublishers());
	    ArrayList<Publisher> publisherList = new ArrayList<Publisher>();
	    while (publishers.next())
	    {
		publisherList.add(new Publisher(publishers.getInt("PublisherID"), publishers.getInt("PublisherCountry"), publishers.getString("PublisherName")));
	    }
	    publishers.close();
	    selectPublishers.close();
	    return publisherList;
	}
	catch (Exception se)
	{
	    ErrorLogger.logErrorMessage(this.getClass().getName(), 636, se.getMessage());
	    return null;
	}
    }

    public void createPublisher(Publisher p)
    {
	try
	{
	    PreparedStatement createPublisher = sqlConn.prepareStatement(StoredProcs.getInsertPreparedStatement(Enums.PUBLISHER));
	    createPublisher.setString(1, p.publisherName);
	    createPublisher.setInt(2, p.publisherCountryID);
	    createPublisher.executeUpdate();
	    createPublisher.close();
	}
	catch (Exception se)
	{
	    ErrorLogger.logErrorMessage(this.getClass().getName(), 653, se.getMessage());
	}
    }
}
